## 21.2. DBI.pm API

The DBI API is the standard database API in Perl. So while MsqPerl and MysqlPerl may be more common in legacy code, all new code should be written with DBI.

use |

    use DBI;
        This must be declared in every Perl program that uses the DBI module.

DBI::available_drivers |

    @available_drivers = DBI->available_drivers;
    @available_drivers = DBI->available_drivers($quiet);

`DBI::available_drivers` returns a list of the available DBD drivers. The function does this by searching the Perl distribution for DBD modules. Unless a true value is passed as the argument, the function will print a warning if two DBD modules of the same name are found in the distribution. In the current Msql-Mysql modules distribution, the driver for mSQL is named `mSQL' and the driver for MySQL is named `mysql'.

#### Example

>     use DBI;

>     my @drivers = DBI->available_drivers;
>     print "All of these drivers are available:\n" . join("\n",@drivers) .
>     				"\nBut we're only interested in mSQL and mysql. :)\n";

DBI::bind_col |

    $result = $statement_handle->bind_col($col_num, \$col_variable, \%unused);

`DBI::bind_col` binds a column of a `SELECT` statement with a Perl variable. Every time that column is accessed or modified, the value of the corresponding variable changes to match. The first argument is the number of the column in the statement, where the first column is number 1. The second argument is a reference to the Perl variable to bind to the column. The optional third argument is a reference to a hash of attributes. This is unused in DBD::mysql and DBD::mSQL. The function returns an undefined value `undef` if the binding fails for some reason.

#### Example

>     use DBI;
>     my $db = DBI->connect('DBI:mSQL:mydata',undef,undef);
>     my $query = "SELECT name, date FROM myothertable";
>     my $myothertable_output = $db->prepare($query);

>     my ($name, $date);
>     $myothertable_output->bind_col(1,\$name,undef);
>     $myothertable_output->bind_col(2,\$date,undef);
>     # $name and $date are now bound to their corresponding fields in the outout.

>     $myothertable_output->execute;
>     while ($myothertable_output->fetch) {
>            # $name and $date are automatically changed each time.
>     				print "Name: $name Date: $date\n";
>     }

DBI::bind_columns |

    $result = $statement_handle->bind_columns(\%unused, @list_of_refs_to_vars);

`DBI::bind_columns` binds an entire list of scalar references to the corresponding field values in the output. The first argument to the function is a reference to a hash of attributes, as in `DBI::bind_col`. DBD::mSQL and DBD::mysql do not use this argument. Each following argument must be a reference to a scalar. Optionally, the scalars can be grouped into a `\($var1, $var2)` structure which has the same effect. There must be exactly as many scalar references as there are fields in the output or the program will die.

#### Example

>     use DBI;
>     my $db = DBI->connect('DBI:mSQL:mydata',undef,undef);
>     my $query = "SELECT name, date FROM myothertable";
>     my $myothertable_output = $db->prepare($query);

>     my ($name, $date);
>     $myothertable_output->bind_columns(undef, \($name, $date));
>     # $name and $date are now bound to their corresponding fields in the outout.

>     $myothertable_output->execute;
>     while ($myothertable_output->fetch) {
>            # $name and $date are automatically changed each time.
>     				print "Name: $name Date: $date\n";
>     }

DBI::bind_param |

    $result = $statement_handle->bind_param($param_number, $bind_value);
    $result = $statement_handle->bind_param($param_number, $bind_value, $bind_type);
    $result = $statement_handle->bind_param($param_number, $bind_value, \%bind_type);

`DBI::bind_param` substitutes real values for the `?' placeholders in statements (see Reference 21.22). The first argument is the number of the placeholder in the statement. The first placeholder (from left to right) is 1. The second argument is the value with which to replace the placeholder. An optional third parameter can be supplied which determines the type of the value to be substituted. This can be supplied as a scalar or as a reference to a hash of the form `{ TYPE => &DBI::SQL_TYPE }` where ``SQL_TYPE`' is the type of the parameter. As of the time of this writing the (undocumented) SQL types supported by DBI are `SQL_CHAR`, `SQL_NUMERIC`, `SQL_DECIMAL`, `SQL_INTEGER`, `SQL_SMALLINT`, `SQL_FLOAT`, `SQL_REAL`, `SQL_DOUBLE,` and `SQL_VARCHAR`. It is not documented how these correspond to the actual types used by DBD::mSQL and DBD::Mysql. However, Table 21-1 contains a list of the corresponding types as of the time of this writing. The function returns `undef` if the substitution is unsuccessful.

#### Table 21-1. Example

DBI | MSQL | MySQL
--- | --- | ---
SQL_CHAR | CHAR_TYPE IDENT_TYPE NULL_TYPE DATE_TYPE MONEY_TYPE TIME_TYPE IDX_TYPE SYSVAR_TYPE ANY_TYPE | FIELD_TYPE_CHAR FIELD_TYPE_DATE FIELD_TYPE_DATETIME FIELD_TYPE_NULL FIELD_TYPE_TIMESTAMP FIELD_TYPE_TIME
SQL_NUMERIC |  | FIELD_TYPE_LONG FIELD_TYPE_LONGLONG FIELD_TYPE_SHORT
SQL_DECIMAL |  | FIELD_TYPE_DECIMAL
SQL_INTEGER | INT_TYPE | FIELD_TYPE_INT24
SQL_SMALLINT | UINT_TYPE | FIELD_TYPE_INT24
SQL_FLOAT |  | FIELD_TYPE_FLOAT
SQL_REAL | REAL_TYPE LAST_REAL_TYPE | FIELD_TYPE_DOUBLE
SQL_DOUBLE |  | FIELD_TYPE_DOUBLE
SQL_VARCHAR | TEXT_TYPE | FIELD_TYPE_TINY_BLOB FIELD_TYPE_MEDIUM_BLOB FIELD_TYPE_BLOB FIELD_TYPE_LONG_BLOB FIELD_TYPE_VAR_STRING FIELD_TYPE_STRING

#### Example

>     use DBI;
>     my $db = DBI->connect('DBD:msql:mydata','me','mypass');
>     my $statement = $db->prepare(
>     "SELECT name, date FROM myothertable WHERE name like ? OR name like ?");

>     $statement->bind_param(1,'J%','SQL_CHAR');
>     $statement->bind_param(2,'%oe%', { TYPE => &DBI::SQL_CHAR });
>     # The statement will now be:
>     # SELECT name, date FROM myothertable WHERE name like 'J%' or name like '%oe%'

DBI::connect |

    $db = DBI->connect($data_source, $username, $password);
    $db = DBI->connect($data_source, $username, $password, \%attributes);

`DBI::connect` requires at least three arguments, with an optional fourth, and returns a handle to the requested database. It is through this handle that you perform all of the transactions with the database server. The first argument is a data source. A list of available data sources can be obtained using `DBI::data_sources`. For mSQL and MySQL the format of the data sources is '`DBI:mSQL:$database:$hostname:$port`' and '`DBI:mysql:$database:$hostname:$port`' respectively. You may leave the ':$port' extension off to connect to the standard port. Also, you may leave the ':$hostname:$port' extension off to connect to a server on the local host using a Unix-style socket. A database name must be supplied.

The second and third arguments are the username and password of the user connecting to the database. For mSQL, these should both be ``undef`'. If they are ``undef`' for MySQL, the user running the program must have permission to access the requested databases.

The final argument is optional and is a reference to an associative array. Using this hash you may preset certain attributes for the connection. Currently, the only supported attributes are PrintError, RaiseError, and AutoCommit. These can be set to for off and some true value for on. The defaults for PrintError and AutoCommit are on and the default for RaiseError is off. Because mSQL and MySQL both do not currently support transactions, the AutoCommit attribute must be set to on (see Reference 21.28 for more details).

If the connection fails, an undefined value `undef` is returned and the error is placed in `$DBI::errstr`.

#### Example

>     use DBI;

>     my $db1 = DBI->connect('DBI:mSQL:mydata',undef,undef);
>     # $db1 is now connected to the local mSQL server using the database 'mydata'.

>     my $db2 = DBI->connect('DBI:mysql:mydata:myserver.com','me','mypassword');
>     # $db2 is now connected to the MySQL server on the default port of
>     # 'myserver.com' using the database 'mydata'. The connection was made with
>     # the username 'me' and the password 'mypassword'.

>     My $db3 = DBI->connect('DBI:mSQL:mydata',undef,undef, {
>     				RaiseError => 1
>     });
>     # $db3 is now connected the same way as $db1 except the 'RaiseError'
>     # attribute has been set to true.

DBI::data_sources |

    @data_sources = DBI->data_sources($dbd_driver);

`DBI::data_sources` takes the name of a DBD module as its argument and returns all of the available databases for that driver in a format suitable for use as a data source in the `DBI::connect` function. The program will die with an error message if an invalid DBD driver name is supplied. In the current Msql-Mysql modules distribution, the driver for mSQL is named `mSQL' and the driver for MySQL is named `mysql'.

#### Example

>     use DBI;

>     my @msql_data_sources = DBI->data_sources('mSQL');
>     my @mysql_data_sources = DBI->data_sources('mysql');
>     # Both DBD::mSQL and DBD::mysql had better be installed or
>     # the program will die.

>     print "mSQL databases:\n" . join("\n",@msql_data_sources) . "\n\n";
>     print "MySQL databases:\n" . join("\n",@mysql_data_sources) . "\n\n";

DBI::do |

    $rows_affected  = $db->do($statement);
    $rows_affected  = $db->do($statement, \%unused);
    $rows_affected  = $db->do($statement, \%unused, @bind_values);

`DBI::do` directly performs a non-`SELECT` SQL statement and returns the number of rows affected by the statement. This is faster than a `DBI::prepare/DBI::execute` pair which requires two function calls. The first argument is the SQL statement itself. The second argument is unused in DBD::mSQL and DBD::mysql, but can hold a reference to a hash of attributes for other DBD modules. The final argument is an array of values used to replace `placeholders,' which are indicated with a `?' in the statement. The values of the array are substituted for the placeholders from left to right. As an additional bonus, `DBI::do` will automatically quote string values before substitution.

#### Example

>     use DBI;
>     my $db = DBI->connect('DBI:mSQL:mydata',undef,undef);

>     my $rows_affected = $db->do("UPDATE mytable SET name='Joe' WHERE name='Bob'");
>     print "$rows_affected Joe's were changed to Bob's\n";

>     my $rows_affected2 = $db->do("INSERT INTO mytable (name) VALUES (?)",
>     				{}, ("Sheldon's Cycle"));
>     # After quoting and substitution, the statement:
>     # INSERT INTO mytable (name) VALUES ('Sheldon's Cycle')
>     # was sent to the database server.

DBI::disconnect |

    $result  = $db->disconnect;

`DBI::disconnect` disconnects the database handle from the database server. With mSQL and MySQL, this is largely unnecessary because the databases do not support transactions and an unexpected disconnect will do no harm. However, databases that do support transactions need to be explicitly disconnected. Therefore, for portable code you should always call disconnect before exiting the program. If there is an error while attempting to disconnect, a nonzero value will be returned and the error will be set in `$DBI::errstr`.

#### Example

>     use DBI;
>     my $db1 = DBI->connect('DBI:mSQL:mydata',undef,undef);
>     my $db2 = DBI->connect('DBI:mSQL:mydata2',undef,undef);
>     ...
>     $db1->disconnect;
>     # The connection to 'mydata' is now severed. The connection to 'mydata2'
>     # is still alive.

DBI::dump_results |

    $neat_rows = DBI::dump_results($statement_handle);
    $neat_rows = DBI::dump_results($statement_handle, $maxlen);
    $neat_rows = DBI::dump_results($statement_handle, $maxlen, $line_sep);
    $neat_rows = DBI::dump_results($statement_handle, $maxlen, $line_sep,
                 $field_sep);
    $neat_rows = DBI::dump_results($statement_handle, $maxlen, $line_sep,
                 $field_sep, $file_handle);

`DBI::dump_results` prints the contents of a statement handle in a neat and orderly fashion by calling `DBI::neat_string` on each row of data. This is useful for quickly checking the results of queries while you write your code. The only required argument is the statement handle to print out. If a second argument is present, it is used as the maximum length of each field in the table. The default is 35. A third argument is the string used to separate each line of data. The default is `\n`. The fourth argument is the string used to join the fields in a row. The default is a comma. The final argument is a reference to a filehandle glob. The results are printed to this filehandle. The default is `STDOUT`. If the statement handle cannot be read, an undefined value `undef` is returned.

#### Example

>     use DBI;
>     my $db = DBI->connect('DBI:mSQL:mydata',undef,undef);
>     my $query = "SELECT name, date FROM myothertable";
>     my $myothertable_output = $db->prepare($query);
>     $myothertable_output->execute;

>     print DBI::dump_results($myothertable_output);
>     # Print the output in a neat table.

>     open(MYOTHERTABLE,">>myothertable");
>     print DBI::dump_results($myothertable_output,undef,undef,undef,\*MYOTHERTABLE);
>     # Print the output again into the file 'myothertable'.

$DBI::err |

    $error_code  = $handle->err;

`$DBI::err` returns the error code for the last DBI error encountered. This error number corresponds to the error message returned from `$DBI::errstr`. The variable `$DBI::err` performs the same function. This function is available from both database and statement handles.

#### Example

>     use DBI;
>     my $db = DBI->connect('DBI:mysql:mydata','webuser','super_secret_squirrel');

>     # There is a parse error in this query...
>     my $output = $db->prepare('SLECT * from mydata');
>     $output->execute;

>     if (not $output) {
>     				print "Error $DBI:err: $DBI:errstr\n";
>     }

$DBI::errstr |

    $error = $handle->errstr;

`$DBI::errstr` returns the error message for the last DBI error encountered. The value remains until the next error occurs, at which time it is replaced. If no error has occurred during your session, the function returns `undef`. The variable `$DBI::errstr` performs the same function. This function is available from both database and statement handles.

#### Example

>     Use DBI;
>     my $db = DBI->connect('DBI:mysql:mydata','webuser','super_secret_squirrel');
>     ...
>     my $error = $db->errstr;
>     warn("This is your most recent DBI error: $error");

DBI::execute |

    $rows_affected = $statement_handle->execute;
    $rows_affected = $statement_handle->execute(@bind_values);

`DBI::execute` executes the SQL statement held in the statement handle. For a non-`SELECT` query, the function returns the number of rows affected. The function returns `-1' if the number of rows is not known. For a `SELECT` query, some true value is returned upon success. If arguments are provided, they are used to fill in any placeholders in the statement (see Reference 21.22).

#### Example

>     use DBI;
>     my $db = DBI->connect('DBI:mSQL:mydata',undef,undef);
>     my $statement_handle = $db->prepare("SELECT * FROM mytable");
>     my $statement_handle2 = $db->prepare("SELECT name, date FROM myothertable
>        WHERE name like ?");

>     $statement_handle->execute;
>     # The first statement has now been performed. The values can now be accessed
>     # through the statement handle.

>     $statement_handle->execute("J%");
>     # The second statement has now been executed as the following:
>     # SELECT name, date FROM myothertable WHERE name like 'J%'

DBI::fetchall_arrayref |

    $ref_of_array_of_arrays = $statement_handle->fetchall_arrayref;

`DBI::fetchall_arrayref` returns all of the remaining data in the statement handle as a reference to an array. Each row of the array is a reference to another array that contains the data in that row. The function returns an undefined value `undef` if there is no data in the statement handle. If any previous `DBI::fetchrow_`* functions were called on this statement handle, `DBI::fetchall_arrayref` returns all of the data after the last `DBI::fetchrow_`* call.

#### Example

>     use DBI;
>     my $db = DBI->connect('DBI:mSQL:mydata',undef,undef);
>     my $query = "SELECT name, date FROM myothertable";
>     my $output = $db->prepare($query);
>     $output->execute;

>     my $data = $output->fetchall_arrayref;
>     # $data is not a reference to an array of arrays. The each element of the
>     # `master' array is itself an array that contains a row of data.

>     print "The fourth date in the table is: " . $data->[3][1] . "\n";
>     # data.
>     # Element 1 of that array is the date.

DBI::fetchrow_array |

    @row_of_data = $statement_handle->fetchrow;

`DBI::fetchrow` returns the next row of data from a statement handle generated by `DBI::execute`. Each successive call to `DBI::fetchrow` returns the next row of data. When there is no more data, the function returns an undefined value `undef`. The elements in the resultant array are in the order specified in the original query. If the query was of the form `SELECT` * `FROM` . . ., the elements are ordered in the same sequence as the fields were defined in the table.

#### Example

>     use DBI;
>     my $db = DBI->connect('DBI:mSQL:mydata',undef,undef);
>     my $query = "SELECT name, date FROM myothertable WHERE name LIKE 'Bob%'";
>     my $myothertable_output = $db->prepare($query);
>     $myothertable_output->execute;

>     my ($name, $date);

>     # This is the first row of data from $myothertable_output.
>     ($name, $date) = $myothertable_output->fetchrow_array;
>     # This is the next row...
>     ($name, $date) = $myothertable_output->fetchrow_array;
>     # And the next...
>     my @name_and_date = $myothertable_output->fetchrow_array;
>     # etc...

DBI::fetchrow_arrayref, DBI::fetch |

    $array_reference = $statement_handle->fetchrow_arrayref;
    $array_reference = $statement_handle->fetch;

`DBI:: fetchrow_arrayref` and its alias, `DBI::fetch`, work exactly like `DBI::fetchrow_array` except that they return a reference to an array instead of an actual array.

#### Example

>     use DBI;
>     my $db = DBI->connect('DBI:mSQL:mydata',undef,undef);
>     my $query = "SELECT name, date FROM myothertable WHERE name LIKE 'Bob%'";
>     my $myothertable_output = $db->prepare($query);
>     $myothertable_output->execute;

>     my $name1 = $myothertable_output->fetch->[0]
>     # This is the 'name' field from the first row of data.
>     my $date2 = $myothertable_output->fetch->[1]
>     # This is the 'date' from from the *second* row of data.
>     my ($name3, $date3) = @{$myothertable_output->fetch};
>     # This is the entire third row of data. $myothertable_output->fetch returns a
>     # reference to an array. We can 'cast' this into a real array with the @{}
>     # construct.

DBI::fetchrow_hashref |

    $hash_reference = $statement_handle->fetchrow_hashref;

`DBI::fetchrow_hashref` works exactly like `DBI::fetchrow_arrayref` except that it returns a reference to an associative array instead of a regular array. The keys of the hash are the names of the fields and the values are the values of that row of data.

#### Example

>     use DBI;
>     my $db = DBI->connect('DBI:mSQL:mydata',undef,undef);
>     my $query = "SELECT * FROM mytable";
>     my $mytable_output = $db->prepare($query);
>     $mytable_output->execute;

>     my %row1 = $mytable_ouput->fetchrow_hashref;
>     my @field_names = keys %row1;
>     # @field_names now contains the names of all of the fields in the query.
>     # This needs to be set only once. All future rows will have the same fields.
>     my @row1 = values %row1;

DBI::finish |

    $result = $statement_handle->finish;

`DBI::finish` releases all data in the statement handle so that the handle may be destroyed or prepared again. Some database servers require this in order to free the appropriate resources. DBD::mSQL and DBD::mysql do not need this function, but for portable code, you should use it after you are done with a statement handle. The function returns an undefined value `undef` if the handle cannot be freed.

#### Example

>     use DBI;
>     my $db = DBI->connect('DBI:mysql:mydata','me','mypassword');
>     my $query = "SELECT * FROM mytable";
>     my $mytable_output = $db->prepare($query);
>     $mytable_output->execute;
>     ...
>     $mytable_output->finish;
>     # You can now reassign $mytable_output or prepare another statement for it.

DBI::func |

    $handle->func(@func_arguments, $func_name);
    @dbs = $db->func("$hostname", '_ListDBs');
    @dbs = $db->func("$hostname:$port", '_ListDBs');
    @tables = $db->func('_ListTables');
    $result = $drh->func( $database, '_CreateDB' );
    $result = $drh->func( $database, '_DropDB' );

`DBI::func` calls specialized nonportable functions included with the various DBD drivers. It can be used with either a database or a statement handle depending on the purpose of the specialized function. If possible, you should use a portable DBI equivalent function. When using a specialized function, the function arguments are passed as a scalar first followed by the function name. DBD::mSQL and DBD::mysql implement the following functions:

`_ListDBs`

  The `_ListDBs` function takes a hostname and optional port number and returns a list of the databases available on that server. It is better to use the portable function `DBI::data_sources`.

`_ListTables`

  The `_ListTables` function returns a list of the tables present in the current database.

`_CreateDB`

  The `_CreateDB` function takes the name of a database as its argument and attempts to create that database on the server. You must have permission to create databases for this function to work. The function returns -1 on failure and on success.

`_DropDB`

  The `_DropDB` function takes the name of a database as its argument and attempts to delete that database from the server. This function does not prompt the user in any way, and if successful, the database will be irrevocably gone forever. You must have permission to drop databases for this function to work. The function returns -1 on failure and on success.

#### Example

>     use DBI;
>     my $db = DBI->connect('DBI:mysql:mydata','me','mypassword');

>     my @tables = $db->func('_ListTables');
>     # @tables now has a list of the tables in 'mydata'.

DBI::neat |

    $neat_string = DBI::neat($string);
    $neat_string = DBI::neat($string, $maxlen);

`DBI::neat` takes as its arguments a string and an optional length. The string is then formatted to print out neatly. The entire string is enclosed in single quotes. All unprintable characters are replaced with periods. If the length argument is present, are characters after the maximum length are removed and the string is terminated with three periods (`...`). If no length is supplied, 400 is used as the default length.

#### Example

>     use DBI;

>     my $string = "This is a very, very, very long string with lots of stuff in it.";
>     my $neat_string = DBI::neat($string,14);
>     # $neat_string is now: 'This is a very...

DBI::neat_list |

    $neat_string = DBI::neat_list(\@listref, $maxlen);
    $neat_string = DBI::neat_list(\@listref, $maxlen, $field_seperator);

`DBI::neat_list` takes three arguments and returns a neatly formatted string suitable for printing. The first argument is a reference to a list of values to print. The second argument is the maximum length of each field. The final argument is a string used to join the fields. `DBI::neat` is called for each member of the list using the maximum length given. The resulting strings are then joined using the last argument. If the final argument is not present, a comma is used as the separator.

#### Example

>     use DBI;

>     my @list = (`Bob', `Joe', `Frank');
>     my $neat_string = DBI::neat_list(\@list, 3);
>     # $neat_string is now: 'Bob', 'Joe', 'Fra...

DBI::prepare |

    $statement_handle = $db->prepare($statement);
    $statement_handle = $db->prepare($statement, \%unused);

`DBI::prepare` takes as its argument an SQL statement, which some database modules put into an internal compiled form so that it runs faster when `DBI::execute` is called. These DBD modules (not DBD::mSQL or DBD::mysql) also accept a reference to a hash of optional attributes. The mSQL and MySQL server do not currently implement the concept of "preparing," so `DBI::prepare` merely stores the statement. You may optionally insert any number of `?' symbols into your statement in place of data values. These symbols are known as "placeholders." The `DBI::bind_param` function is used to substitute the actual values for the placeholders. The function returns `undef` if the statement cannot be prepared for some reason.

#### Example

>     use DBI;
>     my $db = DBI->connect('DBI:mysql:mydata','me','mypassword');

>     my $statement_handle = $db->prepare('SELECT * FROM mytable');
>     # This statement is now ready for execution.

>     My $statement_handle = $db->prepare(
>     'SELECT name, date FROM myothertable WHERE name like ?');
>     # This statement will be ready for exececuting once the placeholder is filled
>     # in using the DBI::bind_param function.

DBI::quote |

    $quoted_string = $db->quote($string);

`DBI::quote` takes a string intended for use in an SQL query and returns a copy that is properly quoted for insertion in the query. This includes placing the proper outer quotes around the string.

#### Example

>     use DBI;
>     my $db1 = DBI->connect('DBI:mSQL:mydata',undef,undef);
>     my $db2 = DBI->connect('DBI:mysql:myotherdata','me','mypassword');

>     my $string = "Sheldon's Cycle";

>     my $qs1 = $db1->quote($string);
>     # $qs1 is: 'Sheldon\'s Cycle' (including the outer quotes)
>     my $qs2 = $db2->quote($string);
>     # $qs2 is: 'Sheldon's Cycle' (including the outer quotes)
>     # Both strings are now suitable for use in a statement for their respective
>     # database servers.

DBI::rows |

    $number_of_rows = $statement_handle->rows;

`DBI::rows` returns the number of rows of data contained in the statement handle. With DBD::mSQL and DBD::mysql, this function is accurate for all statements, including `SELECT` statements. For many other drivers which do not hold of the results in memory at once, this function is only reliable for non-`SELECT` statements. This should be taken into account when writing portable code. The function returns `-1' if the number of rows is unknown for some reason. The variable `$DBI::rows` provides the same functionality.

#### Example

>     use DBI;
>     my $db = DBI->connect('DBI:mSQL:mydata',undef,undef);
>     my $query = "SELECT name, date FROM myothertable WHERE name='Bob'";
>     my $myothertable_output = $db->prepare($query);
>     $myothertable_output->execute;

>     my $rows = $myothertable_output->rows;
>     print "There are $rows 'Bob's in 'myothertable'.\n";

DBI::state |

    $sql_error  = $handle->state;

`DBI::state` returns the `SQLSTATE` SQL error code for the last error DBI encountered. Currently both DBD::mSQL and DBD::mysql report `S1000' for all errors. This function is available from both database and statement handles. The variable `$DBI::state` performs the same function.

#### Example

>     Use DBI;
>     my $db = DBI->connect('DBI:mysql:mydata','webuser','super_secret_squirrel');
>     ...
>     my $sql_error = $db->state;
>     warn("This is your most recent DBI SQL error: $sql_error");

DBI::trace |

    DBI->trace($trace_level)
    DBI->trace($trace_level, $trace_file)
    $handle->trace($trace_level);
    $handle->trace($trace_level, $trace_file);

`DBI::trace` is useful mostly for debugging purposes. If the trace level is set to 2, full debugging information will be displayed. Setting the trace level to disables the trace. If `DBI->trace` is used, tracing is enabled for all handles. If `$handle->trace` is used, tracing is enabled for that handle only. This works for both database and statement handles. If a second argument is present for either `DBI->trace` or `$handle->trace`, the debugging information for all handles is appended to that file. You can turn on tracing also by setting the environment variable `DBI_TRACE`. If the environment variable is defined as a number (0 or 2, currently) tracing for all handles is enabled at that level. With any other definition, the trace level is set to 2 and the value of the environment variable is used as the filename for outputting the trace information.

#### Example

>     use DBI;
>     my $db1 = DBI->connect('DBI:mysql:mydata','webuser','super_secret_squirrel');
>     my $db2 = DBI->connect('DBI:mSQL:myotherdata',undef,undef);

>     DBI->trace(2);
>     # Tracing is now enabled for all handles at level 2.
>     $db2->trace(0);
>     # Tracing is now disabled for $db2, but it is still enabled for $db1
>     $db1->trace(2,'DBI.trace');
>     # Tracing is now enabled for all handles at level 2, with the output being
>     # sent to the file 'DBI.trace'.

DBI::commit, DBI::rollback, DBI::ping |

    $result = $db->commit;
    $result = $db->rollback;
    $result = $db->ping;

`DBI::commit` and `DBI::rollback` are useful only with database servers that support transactions. They have no effect when used with DBD::mSQL and DBD::mysql. `DBD::ping` attempts to verify if the database server is running. It is not implemented and has no effect with DBD::mSQL and DBD::mysql.

Attributes |

    $db->{AutoCommit}
    $handle->{ChopBlanks}
    $handle->{CompatMode}
    $handle->{InactiveDestroy}
    $handle->{LongReadLen}
    $handle->{LongTruncOk}
    $handle->{PrintError}
    $handle->{RaiseError}
    $handle->{Warn}
    $statement_handle->{CursorName}
    $statement_handle->{insertid} (MySQL only)
    $statement_handle->{is_blob} (MySQL only)
    $statement_handle->{is_key} (MySQL only)
    $statement_handle->{is_not_null}
    $statement_handle->{is_num}
    $statement_handle->{is_pri_key} (MySQL and mSQL 1.x only)
    $statement_handle->{length}
    $statement_handle->{max_length} (MySQL only)
    $statement_handle->{NAME}
    $statement_handle->{NULLABLE}
    $statement_handle->{NUM_OF_FIELDS}
    $statement_handle->{NUM_OF_PARAMS}
    $statement_handle->{table}
    $statement_handle->{type}

The DBI.pm API defines several attributes that may be set or read at any time. Assigning a value to an attribute that can be set changes the behavior of the current connection in some way. Assigning any true value to an attribute will set that attribute on. Assigning to an attribute sets it off. Some values are defined only for particular databases and are not portable. The following are attributes that are present for both database and statement handles.

`$db->{AutoCommit}`

  This attribute affects the behavior of database servers that support transactions. For mSQL and MySQL, they must always be set to `on' (the default). Attempting to change this will kill the program.

`$handle->{ChopBlanks}`

  If this attribute is on, any data returned from a query (such as `DBI::fetchrow` call) will have any leading or trailing spaces chopped off. Any handles deriving from the current handle inherit this attribute. The default for this attribute is `off.'

`$handle->{InactiveDestroy}`

  This attribute is designed to enable handles to survive a `fork' so that a child can make use of a parent's handle. You should enable this attribute in either the parent or the child but not both. The default for this attribute is `off.'

`$handle->{PrintError}`

  If this attribute is on, all warning messages will be displayed to the user. If this attribute is off, the errors are available only through `$DBI::errstr`. Any handles deriving from the current handle inherit this attribute. The default for this attribute is `on.'

`$handle->{RaiseError}`

  If this attribute is on, any errors will raise an exception in the program, killing the program if no ``__DIE__`' handler is defined. Any handles deriving from the current handle inherit this attribute. The default for this attribute is `off.'

`$handle->{Warn}`

  If this attribute is on, warning messages for certain bad programming practices (most notably holdovers from Perl 4) will be displayed. Turning this attribute off disables DBI warnings and should be used only if you are really confident in your programming skills. Any handles deriving from the current handle (such as a statement handle resulting from a database handle query) inherit this attribute. The default for this attribute is `on.'

`$statement_handle->{insertid}`

  This is a nonportable attribute that is defined only for DBD::mysql. The attribute returns the current value of the `auto_increment` field (if there is one) in the table. If no `auto_increment` field exists, the attribute returns `undef`.

`$statement_handle->{is_blob}`

  This is a nonportable attribute which is defined only for DBD::mysql. The attribute returns a reference to an array of boolean values indicating if each of the fields contained in the statement handle is of a `BLOB` type. For a statement handle that was not returned by a `SELECT` statement, `$statement_handle->{is_blob}` returns `undef`.

`$statement_handle->{is_key}`

  This is a nonportable attribute which is defined only for DBD::mysql. The attribute returns a reference to an array of boolean values indicating if each of the fields contained in the statement handle were defined as a `KEY`. For a statement handle that was not returned by a `SELECT` statement, `$statement_handle->{is_key}` returns `undef`.

`$statement_handle->{is_not_null}`

  This is a nonportable attribute which is defined only for DBD::mSQL and DBD::mysql. The attribute returns a reference to a list of boolean values indicating if each of the fields contained in the statement handle are defined ``NOT` `NULL`'. For a statement handle that was not returned by a `SELECT` statement, `$statement_handle->{is_not_null}` returns `undef`. The same effect of this attribute can be accomplished in a portable manner by using `$statement_handle->{NULLABLE}`.

`$statement_handle->{is_num}`

  This is a nonportable attribute which is defined only for DBD::mSQL and DBD::mysql. The attribute returns a reference to an array of boolean values indicating if each of the fields contained in the statement handle is a number type. For a statement handle that was not returned by a `SELECT` statement, `$statement_handle->{is_num}` returns `undef`.

`$statement_handle->{is_pri_key}`

  This is a nonportable attribute which is defined only for DBD::mSQL and DBD::mysql. When used with DBD::mSQL it has effect only in conjunction with mSQL 1.x servers, because mSQL 2.x does not use primary keys. The attribute returns a reference to a list of boolean values indicating if each of the fields contained in the statement handle is a primary key. For a statement handle that was not returned by a `SELECT` statement, `$statement_handle->{is_pri_key}` returns `undef`.

`$statement_handle->{length}`

  This is a nonportable attribute which is defined only for DBD::mysql and DBD::mSQL. The attribute returns a reference to a list of the maximum possible length of each field contained in the statement handle. For a statement handle that was not returned by a `SELECT` statement, `$statement_handle->{length}` returns `undef`.

`$statement_handle->{max_length}`

  This is a nonportable attribute which is defined only for DBD::mysql. The attribute returns a reference to a list of the actual maximum length of each field contained in the statement handle. For a statement handle that was not returned by a `SELECT` statement, `$statement_handle->{max_length}` returns `undef`.

`$statement_handle->{NAME}`

  This attribute returns a reference to a list of the names of the fields contained in the statement handle. For a statement handle that was not returned by a `SELECT` statement, `$statement_handle->{NAME}` returns `undef`.

`$statement_handle->{NULLABLE}`

  This attribute returns a reference to a list of boolean values indicating if each of the fields contained in the statement handle can have a `NULL` value. A field defined with ``NOT NULL`' will have a value of in the list. All other fields will have a value of 1. For a statement handle that was not returned by a `SELECT` statement, `$statement_handle->{NULLABLE}` returns `undef`.

`$statement_handle->{NUM_OF_FIELDS}`

  This attribute returns the number of columns of data contained in the statement handle. For a statement handle that was not returned by a `SELECT` statement, `$statement_handle->{NUM_OF_FIELDS}` returns 0.

`$statement_handle->{NUM_OF_PARAMS}`

  This attribute returns the number of "placeholders" in the statement handle. Placeholders are indicated with a `?' in the statement. The `DBI::bind_values` function is used to replace the placeholders with the proper values.

`$statement_handle->{table}`

  This is a nonportable attribute which is defined only for DBD::mSQL and DBD::mysql. The attribute returns a reference to a list of the names of the tables accessed in the query. This is particularly useful in conjunction with a JOINed SELECT that uses multiple tables.

`$statement_handle->{type}`

  This is a nonportable attribute which is defined only for DBD::mSQL and DBD::mysql. The attribute returns a reference to a list of the types of the fields contained in the statement handle. For a statement handle that was not returned by a SELECT statement, `$statement_handle->{max_length}` returns `undef`. The values of this list are integers that correspond to an enumeration in the mysql_com.h C header file found in the MySQL distribution. There is currently no method to access the names of these types from within DBI. But the types are accessible via the`&Mysql::FIELD_TYPE_*` function in Mysql.pm. There is also an undocumented attribute in DBD::mysql called `$statement_handle->{format_type_name}` which is identical to `$statement_handle->{type}` except that it returns the SQL names of the types instead of integers. It should be stressed that this is an undocumented attribute and the author of DBD::mysql has stated his intention to remove it should DBI implement the same functionality.

`$statement_handle->{CursorName}`
`$handle->{LongReadLen}`
`$handle->{LongTruncOk}`
`$handle->{CompatMode}`

  All of these attributes are unsupported in DBD::mSQL and DBD::mysql. Assigning to them will do nothing and reading them will return a or `undef`. The exception is `$statement_handle->{CursorName}`. Currently, accessing this attribute in any way will cause the program to die.

#### Example

>     use DBI;
>     my $db = DBI->connect('mysql:mydata','me','mypassword');

>     $db->{RAISE_ERROR} = 1;
>     # Now, any DBI/DBD errors will kill the program.

>     my $statement_handle = $db->prepare('SELECT * FROM mytable');
>     $statement_handle->execute;

>     my @fields = @{$statement_handle->{NAME}};
>     # @fields now contains an array of all of the field names in 'mytable'.

Copyright © 2001 O'Reilly & Associates. All rights reserved.